package com.apes.fn.server.productReceive;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;


import java.util.Date;
import java.util.List;
import java.util.Map;

public interface ProductReceiveRepository extends CustomRepository<ProductReceive, String> {
    /**
     * fetch data by local Permit id
     *
     * @param localPermitId 本地权限id
     * @return List<ProductReceive>
     */
    @Query("select T from ProductReceive T " +
            "where T.state='approve' and exists (select 1 from LocalPermitItem L where L.type.id='pt_location'  and T.location.id =L.orgId and L.localPermit.id =?1)" +
            " and exists ( select 1 from ProductReceiveItem K where T.id = K.productReceive.id and K.servicePlanItem.servicePlan.state not in ('qualityTest', 'receivables'))")
    List<ProductReceive> findAllByLocalPermitId(String localPermitId);

    @Query("select T from ProductReceive T " +
            "where T.state='create' " +
            "and exists ( select 1 from ProductReceiveItem K where T.id = K.productReceive.id and K.servicePlanItem.id in :sitemList)")
    List<ProductReceive> findEndOrderByPlanItems(@Param("sitemList")List sitemList);

    @Query("select T from ProductReceive T " +
            "where exists ( select 1 from ProductReceiveItem K where T.id = K.productReceive.id and K.servicePlanItem.id in :sitemList)")
    List<ProductReceive> findByPlanItems(@Param("sitemList")List sitemList);

    @Query("select I from ProductReceive T,ProductReceiveItem I WHERE I.productReceive.id = T.id and T.state <> 'delete' and I.servicePlanItem.id = :servicePlanId")
    ProductReceiveItem findProductReceiveItemByStateNotDeleteAndServicePlanItem(@Param("servicePlanId")String servicePlanId);
    @Query("select I from ProductReceive T,ProductReceiveItem I WHERE I.productReceive.id = T.id and T.state <> 'delete' and I.servicePlanItem.id = :servicePlanId")
    List<ProductReceiveItem> findbyServicePlanItem(@Param("servicePlanId")String servicePlanId);

    @Query("select sum(p.qtyReceive) as qtyReceive from ProductReceiveItem p where p.productReceive.location.depot.dept.id = :deptId " +
            "and p.productReceive.state = 'approve' and p.productReceive.approveDate > :startDate and p.productReceive.approveDate < :endDate ")
    Map<String, Object> findProductReceiveQty(@Param("deptId") String deptId, Date startDate, Date endDate);

}
